import os
import sqlite3
import hashlib

class Db:
    def __init__(self, filename):
        self.filename = filename
        self.modified = os.path.getmtime(filename)
        self.id = self.uniqueId(os.path.basename(filename))
        self.dbfilename = os.path.join(os.path.dirname(os.path.abspath(__file__)), "cache.db")
        self.conn = sqlite3.connect(self.dbfilename)
        self.createTables()

    def uniqueId(self, filename):
        if not filename:
            return self.id
        name_modified = f"{filename}_{self.modified}"
        return hashlib.md5(name_modified.encode()).hexdigest()

    def createTables(self):
        cursor = self.conn.cursor()
        cursor.execute("PRAGMA synchronous = OFF;")
        cursor.execute("PRAGMA journal_mode = WAL;")
        cursor.execute("CREATE TABLE IF NOT EXISTS metadata (id TEXT PRIMARY KEY, "
                       "size INTEGER, duration INTEGER, bitrate INTEGER, framerate REAL, "
                       "codec TEXT, audio TEXT, width INTEGER, height INTEGER);")
        cursor.execute("CREATE TABLE IF NOT EXISTS capture (id TEXT PRIMARY KEY, "
                       " at8 BLOB, at16 BLOB, at24 BLOB, at32 BLOB, at40 BLOB, at48 BLOB, "
                       "at56 BLOB, at64 BLOB, at72 BLOB, at80 BLOB, at88 BLOB, at96 BLOB);")
        cursor.execute("CREATE TABLE IF NOT EXISTS version (version TEXT PRIMARY KEY);")
        cursor.execute("INSERT OR REPLACE INTO version VALUES('1.0');")
        self.conn.commit()

    def readMetadata(self, video):
        cursor = self.conn.cursor()
        cursor.execute(f"SELECT * FROM metadata WHERE id = '{self.id}';")
        row = cursor.fetchone()
        if row:
            video.modified = self.modified
            video.size = row[1]
            video.duration = row[2]
            video.bitrate = row[3]
            video.framerate = row[4]
            video.codec = row[5]
            video.audio = row[6]
            video.width = row[7]
            video.height = row[8]
            return True
        return False

    def writeMetadata(self, video):
        cursor = self.conn.cursor()
        cursor.execute(
            f"INSERT OR REPLACE INTO metadata VALUES('{self.id}',{video.size},{video.duration},{video.bitrate},{video.framerate},'{video.codec}','{video.audio}',{video.width},{video.height});")
        self.conn.commit()

    def readCapture(self, percent):
        cursor = self.conn.cursor()
        cursor.execute(f"SELECT at{percent} FROM capture WHERE id = '{self.id}';")
        row = cursor.fetchone()
        if row:
            return row[0]
        return None

    def writeCapture(self, percent, image):
        cursor = self.conn.cursor()
        cursor.execute(f"INSERT OR IGNORE INTO capture (id) VALUES('{self.id}');")
        cursor.execute(f"UPDATE capture SET at{percent} =? WHERE id = '{self.id}';", (image,))
        self.conn.commit()

    def removeVideo(self, id):
        cursor = self.conn.cursor()
        cursor.execute(f"SELECT id FROM metadata WHERE id = '{id}';")
        row = cursor.fetchone()
        if not row:
            return False
        cursor.execute(f"DELETE FROM metadata WHERE id = '{id}';")
        cursor.execute(f"DELETE FROM capture WHERE id = '{id}';")
        cursor.execute(f"SELECT id FROM metadata WHERE id = '{id}';")
        row = cursor.fetchone()
        if row:
            return False
        return True